import java.sql.*; // imports the JDBC core package
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class CSVgenerator {
	
	private HashMap<Integer, String> FacIDtoFacTypeMap;
	


	public CSVgenerator() {
		super();
		this.FacIDtoFacTypeMap = new HashMap<Integer,String>();
	}

	public void parseDB(){
		float xCoord;
		float yCoord;
		int facTypeInt;
		String facTypeString;
		String Borough;
//		float xmin = 920000;
//		float xmax = 930000;
//		float ymin = 120000;
//		float ymax = 130000;		
		float minXCoord = 913394;
		float maxXCoord = 1066851;
		float minYCoord = 119406;
		float maxYCoord = 272004;
		float xStep = (maxXCoord - minXCoord)/60;
		float yStep = (maxYCoord - minYCoord)/60;
		
		for (float x = minXCoord; x <= maxXCoord-xStep; x+=xStep) {
			for (float y = minYCoord; y <= maxYCoord-yStep; y+=yStep) {
				// SQL Query string
				String query = "SELECT distinct FACILITY_TYPE "
						+ "FROM tblALLfac " + "WHERE XCOORD > " + x
						+ " AND XCOORD < " + (x+xStep) + "AND YCOORD > " + y
						+ "AND YCOORD < " + (y+yStep);
				try {
					Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // load the JDBC driver
					Connection con = DriverManager
							.getConnection("jdbc:odbc:Fac-Access");
					// get a connection
					Statement stmt = con.createStatement();
					ResultSet rs = stmt.executeQuery(query); // execute query
					int cnt = 0;
					ArrayList<String> res = new ArrayList<String>();
					while (rs.next()) { // parse the results
						//xCoord = rs.getFloat("XCOORD");
						//yCoord = rs.getFloat("YCOORD");
						facTypeInt = rs.getInt("FACILITY_TYPE");
						//Borough = rs.getString("BOROUGH");
						facTypeString = FacIDtoFacTypeMap.get(facTypeInt);
						facTypeString = facTypeString.replaceAll(",", " ");
//						System.out.print("("+xCoord + ", " + yCoord + ": " + facTypeString+"),\t");
//						System.out.print(facTypeString+",\t");
						res.add(facTypeString);
						cnt++;
					}
					int size = res.size();
					
					if (size != 0) {
						for (int i = 0; i < size; i++) {
							System.out.print(res.get(i));
							if (i != (size - 1))
								System.out.print(",");
						}
						System.out.println();
					}
					con.close();
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}//end for y
		}//end for x
	}
	
	public void parseFacType(){

		int facTypeInt;
		String facTypeString;

		// SQL Query string
		String query = 	"SELECT FACILITY_TYPE,FACILITY_TYPE_Decode " +
						"FROM Factype";
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // load the JDBC driver
			Connection con = DriverManager.getConnection ("jdbc:odbc:Fac-Access");
			// get a connection
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query); // execute query
			int cnt = 0;
			while (rs.next()) { // parse the results
				facTypeInt = rs.getInt("FACILITY_TYPE");
				facTypeString = rs.getString("FACILITY_TYPE_Decode");
				FacIDtoFacTypeMap.put(facTypeInt, facTypeString);
				cnt++;
			}
			
			con.close();
		}
		catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
		
	public static void main(String[] args){
		CSVgenerator gen = new CSVgenerator();
		gen.parseFacType();
		gen.parseDB();
	}
}
